﻿/*
 ******************************************************************
 *      
 * 域名空间　	: GreatB2C.DALSQLServer
 * 类　名　称	: Contacter.cs
 * 类　功　能	: 数据操作实现层
 * 类　流　程    :                             
 * 运用算法　	:                             
 * 编　写　人	: WHL             
 * 编写日期　	: 2011-3-28
 * 最近更新日期　:
 * 版　　本　	: V1.0                           
 * 描　　述　	: Descript of Class                                                 
 *                                        
 ******************************************************************
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using GreatB2C.Model;
using GreatB2C.DALFactory;
using GreatB2C.DALProfile;
using GreatB2C.Config;
using GreatB2C.Utility;

namespace GreatB2C.DALSQLServer
{
    /// <summary>
    /// 类描述：
    /// </summary>
    [Serializable]
    public class Contacter : DbBase, IContacter
    {
        #region implement methods

        /// <summary>
        /// 获取参数参数列表
        /// </summary>
        /// <param name="ac"></param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(GreatB2C.Model.Contacter ac)
        {
            int i = 0;
            SqlParameter[] param = new SqlParameter[0];

            param[i] = new SqlParameter("@ContacterID", SqlDbType.Int, 4);
            param[i++].Value = ac.ContacterID;

            param[i] = new SqlParameter("@ClientID", SqlDbType.Int, 4);
            param[i++].Value = ac.ClientID;

            param[i] = new SqlParameter("@ParentID", SqlDbType.Int, 4);
            param[i++].Value = ac.ParentID;

            param[i] = new SqlParameter("@UserName", SqlDbType.NVarChar, 20);
            param[i++].Value = ac.UserName;

            param[i] = new SqlParameter("@UserType", SqlDbType.Int, 4);
            param[i++].Value = ac.UserType;

            param[i] = new SqlParameter("@TrueName", SqlDbType.NVarChar, 20);
            param[i++].Value = ac.TrueName;

            param[i] = new SqlParameter("@Sex", SqlDbType.Int, 4);
            param[i++].Value = ac.Sex;

            param[i] = new SqlParameter("@Title", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Title;

            param[i] = new SqlParameter("@Company", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.Company;

            param[i] = new SqlParameter("@Department", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Department;

            param[i] = new SqlParameter("@Position", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Position;

            param[i] = new SqlParameter("@Operation", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Operation;

            param[i] = new SqlParameter("@CompanyAddress", SqlDbType.NVarChar, 200);
            param[i++].Value = ac.CompanyAddress;

            param[i] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.Email;

            param[i] = new SqlParameter("@Homepage", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.Homepage;

            param[i] = new SqlParameter("@QQ", SqlDbType.NVarChar, 20);
            param[i++].Value = ac.QQ;

            param[i] = new SqlParameter("@ICQ", SqlDbType.NVarChar, 25);
            param[i++].Value = ac.ICQ;

            param[i] = new SqlParameter("@MSN", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.MSN;

            param[i] = new SqlParameter("@Yahoo", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.Yahoo;

            param[i] = new SqlParameter("@UC", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.UC;

            param[i] = new SqlParameter("@Aim", SqlDbType.NVarChar, 100);
            param[i++].Value = ac.Aim;

            param[i] = new SqlParameter("@OfficePhone", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.OfficePhone;

            param[i] = new SqlParameter("@HomePhone", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.HomePhone;

            param[i] = new SqlParameter("@PHS", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.PHS;

            param[i] = new SqlParameter("@Fax", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.Fax;

            param[i] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.Mobile;

            param[i] = new SqlParameter("@Country", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Country;

            param[i] = new SqlParameter("@Province", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.Province;

            param[i] = new SqlParameter("@City", SqlDbType.NVarChar, 30);
            param[i++].Value = ac.City;

            param[i] = new SqlParameter("@Address", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.Address;

            param[i] = new SqlParameter("@ZipCode", SqlDbType.NVarChar, 10);
            param[i++].Value = ac.ZipCode;

            param[i] = new SqlParameter("@NativePlace", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.NativePlace;

            param[i] = new SqlParameter("@Nation", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Nation;

            param[i] = new SqlParameter("@Birthday", SqlDbType.DateTime, 8);
            param[i++].Value = ac.Birthday;

            param[i] = new SqlParameter("@IDCard", SqlDbType.NVarChar, 20);
            param[i++].Value = ac.IDCard;

            param[i] = new SqlParameter("@Marriage", SqlDbType.Int, 4);
            param[i++].Value = ac.Marriage;

            param[i] = new SqlParameter("@Family", SqlDbType.NVarChar, 16);
            param[i++].Value = ac.Family;

            param[i] = new SqlParameter("@Income", SqlDbType.Int, 4);
            param[i++].Value = ac.Income;

            param[i] = new SqlParameter("@Education", SqlDbType.Int, 4);
            param[i++].Value = ac.Education;

            param[i] = new SqlParameter("@GraduateFrom", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.GraduateFrom;

            param[i] = new SqlParameter("@InterestsOfLife", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.InterestsOfLife;

            param[i] = new SqlParameter("@InterestsOfCulture", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.InterestsOfCulture;

            param[i] = new SqlParameter("@InterestsOfAmusement", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.InterestsOfAmusement;

            param[i] = new SqlParameter("@InterestsOfSport", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.InterestsOfSport;

            param[i] = new SqlParameter("@InterestsOfOther", SqlDbType.NVarChar, 255);
            param[i++].Value = ac.InterestsOfOther;

            param[i] = new SqlParameter("@CreateTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.CreateTime;

            param[i] = new SqlParameter("@UpdateTime", SqlDbType.DateTime, 8);
            param[i++].Value = ac.UpdateTime;

            param[i] = new SqlParameter("@Owner", SqlDbType.NVarChar, 50);
            param[i++].Value = ac.Owner;


            return param;
        }

        /// <summary>
        /// 检查是否有重复记录。
        /// 注意：这里要手动修改一下，需要加入次主键做为第二条件（主要是为了修改操作）
        /// </summary>
        /// <param name="objModel">对应的数据映射类</param>
        /// <returns>操作结果：true-成功 false-失败</returns>
        public bool IsExists(GreatB2C.Model.Contacter objModel)
        {
            string Sql = "SELECT Count(*) "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Contacter]"
                            + " WHERE "
                            + "	[ContacterID] = @ContacterID"
                //+" and 次主键=@次主键"
                            ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int count = Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, Sql, param).ToString());
                if (count > 0)
                {
                    return true;
                }

                return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 检索出表中所有记录	
        /// </summary>
        /// <param name="aryResult">返回对象数据集合</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public DataTable ListContacter()
        {
            DataTable dtData = new DataTable();
            string Sql = "SELECT "
                            + "[ContacterID],"
                            + "[ClientID],"
                            + "[ParentID],"
                            + "[UserName],"
                            + "[UserType],"
                            + "[TrueName],"
                            + "[Sex],"
                            + "[Title],"
                            + "[Company],"
                            + "[Department],"
                            + "[Position],"
                            + "[Operation],"
                            + "[CompanyAddress],"
                            + "[Email],"
                            + "[Homepage],"
                            + "[QQ],"
                            + "[ICQ],"
                            + "[MSN],"
                            + "[Yahoo],"
                            + "[UC],"
                            + "[Aim],"
                            + "[OfficePhone],"
                            + "[HomePhone],"
                            + "[PHS],"
                            + "[Fax],"
                            + "[Mobile],"
                            + "[Country],"
                            + "[Province],"
                            + "[City],"
                            + "[Address],"
                            + "[ZipCode],"
                            + "[NativePlace],"
                            + "[Nation],"
                            + "[Birthday],"
                            + "[IDCard],"
                            + "[Marriage],"
                            + "[Family],"
                            + "[Income],"
                            + "[Education],"
                            + "[GraduateFrom],"
                            + "[InterestsOfLife],"
                            + "[InterestsOfCulture],"
                            + "[InterestsOfAmusement],"
                            + "[InterestsOfSport],"
                            + "[InterestsOfOther],"
                            + "[CreateTime],"
                            + "[UpdateTime],"
                            + "[Owner]"
                            + " FROM "
                            + "[dbo].[" + Prefix + "Contacter]";

            try
            {
                return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据表中主键ID查询记录信息
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <param name="DesModel">输出的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public GreatB2C.Model.Contacter ListByID(GreatB2C.Model.Contacter objModel)
        {
            string Sql = "SELECT * "
                            + " FROM "
                            + "[dbo].[" + Prefix + "Contacter]"
                            + " WHERE "
                            + "	[ContacterID] = @ContacterID";

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                using (DataTable dtData = DbHelper.ExecuteTable(CommandType.Text, Sql, param))
                {
                    if (dtData.Rows.Count > 0)
                    {
                        GreatB2C.Model.Contacter modClass = new GreatB2C.Model.Contacter();

                        modClass.ContacterID = Convert.ToInt32(dtData.Rows[0]["ContacterID"].ToString());
                        modClass.ClientID = Convert.ToInt32(dtData.Rows[0]["ClientID"].ToString());
                        modClass.ParentID = Convert.ToInt32(dtData.Rows[0]["ParentID"].ToString());
                        modClass.UserName = dtData.Rows[0]["UserName"].ToString();
                        modClass.UserType = Convert.ToInt32(dtData.Rows[0]["UserType"].ToString());
                        modClass.TrueName = dtData.Rows[0]["TrueName"].ToString();
                        modClass.Sex = Convert.ToInt32(dtData.Rows[0]["Sex"].ToString());
                        modClass.Title = dtData.Rows[0]["Title"].ToString();
                        modClass.Company = dtData.Rows[0]["Company"].ToString();
                        modClass.Department = dtData.Rows[0]["Department"].ToString();
                        modClass.Position = dtData.Rows[0]["Position"].ToString();
                        modClass.Operation = dtData.Rows[0]["Operation"].ToString();
                        modClass.CompanyAddress = dtData.Rows[0]["CompanyAddress"].ToString();
                        modClass.Email = dtData.Rows[0]["Email"].ToString();
                        modClass.Homepage = dtData.Rows[0]["Homepage"].ToString();
                        modClass.QQ = dtData.Rows[0]["QQ"].ToString();
                        modClass.ICQ = dtData.Rows[0]["ICQ"].ToString();
                        modClass.MSN = dtData.Rows[0]["MSN"].ToString();
                        modClass.Yahoo = dtData.Rows[0]["Yahoo"].ToString();
                        modClass.UC = dtData.Rows[0]["UC"].ToString();
                        modClass.Aim = dtData.Rows[0]["Aim"].ToString();
                        modClass.OfficePhone = dtData.Rows[0]["OfficePhone"].ToString();
                        modClass.HomePhone = dtData.Rows[0]["HomePhone"].ToString();
                        modClass.PHS = dtData.Rows[0]["PHS"].ToString();
                        modClass.Fax = dtData.Rows[0]["Fax"].ToString();
                        modClass.Mobile = dtData.Rows[0]["Mobile"].ToString();
                        modClass.Country = dtData.Rows[0]["Country"].ToString();
                        modClass.Province = dtData.Rows[0]["Province"].ToString();
                        modClass.City = dtData.Rows[0]["City"].ToString();
                        modClass.Address = dtData.Rows[0]["Address"].ToString();
                        modClass.ZipCode = dtData.Rows[0]["ZipCode"].ToString();
                        modClass.NativePlace = dtData.Rows[0]["NativePlace"].ToString();
                        modClass.Nation = dtData.Rows[0]["Nation"].ToString();
                        modClass.Birthday = Convert.ToDateTime(dtData.Rows[0]["Birthday"].ToString());
                        modClass.IDCard = dtData.Rows[0]["IDCard"].ToString();
                        modClass.Marriage = Convert.ToInt32(dtData.Rows[0]["Marriage"].ToString());
                        modClass.Family = dtData.Rows[0]["Family"].ToString();
                        modClass.Income = Convert.ToInt32(dtData.Rows[0]["Income"].ToString());
                        modClass.Education = Convert.ToInt32(dtData.Rows[0]["Education"].ToString());
                        modClass.GraduateFrom = dtData.Rows[0]["GraduateFrom"].ToString();
                        modClass.InterestsOfLife = dtData.Rows[0]["InterestsOfLife"].ToString();
                        modClass.InterestsOfCulture = dtData.Rows[0]["InterestsOfCulture"].ToString();
                        modClass.InterestsOfAmusement = dtData.Rows[0]["InterestsOfAmusement"].ToString();
                        modClass.InterestsOfSport = dtData.Rows[0]["InterestsOfSport"].ToString();
                        modClass.InterestsOfOther = dtData.Rows[0]["InterestsOfOther"].ToString();
                        modClass.CreateTime = Convert.ToDateTime(dtData.Rows[0]["CreateTime"].ToString());
                        modClass.UpdateTime = Convert.ToDateTime(dtData.Rows[0]["UpdateTime"].ToString());
                        modClass.Owner = dtData.Rows[0]["Owner"].ToString();

                        return modClass;
                    }
                }

                return null;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 新增一条新记录到表中
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Insert(GreatB2C.Model.Contacter objModel)
        {
            string Sql = "INSERT INTO "
                        + "[dbo].[" + Prefix + "Contacter]"
                        + "("
                        + "[ClientID],"
                        + "[ParentID],"
                        + "[UserName],"
                        + "[UserType],"
                        + "[TrueName],"
                        + "[Sex],"
                        + "[Title],"
                        + "[Company],"
                        + "[Department],"
                        + "[Position],"
                        + "[Operation],"
                        + "[CompanyAddress],"
                        + "[Email],"
                        + "[Homepage],"
                        + "[QQ],"
                        + "[ICQ],"
                        + "[MSN],"
                        + "[Yahoo],"
                        + "[UC],"
                        + "[Aim],"
                        + "[OfficePhone],"
                        + "[HomePhone],"
                        + "[PHS],"
                        + "[Fax],"
                        + "[Mobile],"
                        + "[Country],"
                        + "[Province],"
                        + "[City],"
                        + "[Address],"
                        + "[ZipCode],"
                        + "[NativePlace],"
                        + "[Nation],"
                        + "[Birthday],"
                        + "[IDCard],"
                        + "[Marriage],"
                        + "[Family],"
                        + "[Income],"
                        + "[Education],"
                        + "[GraduateFrom],"
                        + "[InterestsOfLife],"
                        + "[InterestsOfCulture],"
                        + "[InterestsOfAmusement],"
                        + "[InterestsOfSport],"
                        + "[InterestsOfOther],"
                        + "[CreateTime],"
                        + "[UpdateTime],"
                            + "[Owner]"
                        + ") VALUES ("
                        + "@ClientID,"
                        + "@ParentID,"
                        + "@UserName,"
                        + "@UserType,"
                        + "@TrueName,"
                        + "@Sex,"
                        + "@Title,"
                        + "@Company,"
                        + "@Department,"
                        + "@Position,"
                        + "@Operation,"
                        + "@CompanyAddress,"
                        + "@Email,"
                        + "@Homepage,"
                        + "@QQ,"
                        + "@ICQ,"
                        + "@MSN,"
                        + "@Yahoo,"
                        + "@UC,"
                        + "@Aim,"
                        + "@OfficePhone,"
                        + "@HomePhone,"
                        + "@PHS,"
                        + "@Fax,"
                        + "@Mobile,"
                        + "@Country,"
                        + "@Province,"
                        + "@City,"
                        + "@Address,"
                        + "@ZipCode,"
                        + "@NativePlace,"
                        + "@Nation,"
                        + "@Birthday,"
                        + "@IDCard,"
                        + "@Marriage,"
                        + "@Family,"
                        + "@Income,"
                        + "@Education,"
                        + "@GraduateFrom,"
                        + "@InterestsOfLife,"
                        + "@InterestsOfCulture,"
                        + "@InterestsOfAmusement,"
                        + "@InterestsOfSport,"
                        + "@InterestsOfOther,"
                        + "@CreateTime,"
                        + "@UpdateTime,"
                            + "@Owner"
                        + ")";

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据主键ID更新指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Update(GreatB2C.Model.Contacter objModel)
        {
            string Sql = "UPDATE "
                        + "[dbo].[" + Prefix + "Contacter]"
                        + " SET "
                        + "[ClientID] = @ClientID,"
                        + "[ParentID] = @ParentID,"
                        + "[UserName] = @UserName,"
                        + "[UserType] = @UserType,"
                        + "[TrueName] = @TrueName,"
                        + "[Sex] = @Sex,"
                        + "[Title] = @Title,"
                        + "[Company] = @Company,"
                        + "[Department] = @Department,"
                        + "[Position] = @Position,"
                        + "[Operation] = @Operation,"
                        + "[CompanyAddress] = @CompanyAddress,"
                        + "[Email] = @Email,"
                        + "[Homepage] = @Homepage,"
                        + "[QQ] = @QQ,"
                        + "[ICQ] = @ICQ,"
                        + "[MSN] = @MSN,"
                        + "[Yahoo] = @Yahoo,"
                        + "[UC] = @UC,"
                        + "[Aim] = @Aim,"
                        + "[OfficePhone] = @OfficePhone,"
                        + "[HomePhone] = @HomePhone,"
                        + "[PHS] = @PHS,"
                        + "[Fax] = @Fax,"
                        + "[Mobile] = @Mobile,"
                        + "[Country] = @Country,"
                        + "[Province] = @Province,"
                        + "[City] = @City,"
                        + "[Address] = @Address,"
                        + "[ZipCode] = @ZipCode,"
                        + "[NativePlace] = @NativePlace,"
                        + "[Nation] = @Nation,"
                        + "[Birthday] = @Birthday,"
                        + "[IDCard] = @IDCard,"
                        + "[Marriage] = @Marriage,"
                        + "[Family] = @Family,"
                        + "[Income] = @Income,"
                        + "[Education] = @Education,"
                        + "[GraduateFrom] = @GraduateFrom,"
                        + "[InterestsOfLife] = @InterestsOfLife,"
                        + "[InterestsOfCulture] = @InterestsOfCulture,"
                        + "[InterestsOfAmusement] = @InterestsOfAmusement,"
                        + "[InterestsOfSport] = @InterestsOfSport,"
                        + "[InterestsOfOther] = @InterestsOfOther,"
                        + "[CreateTime] = @CreateTime,"
                        + "[UpdateTime] = @UpdateTime,"
                            + "[Owner] = @Owner"
                        + " WHERE "
                        + "[ContacterID] = @ContacterID"
                        ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据主键删除指定记录
        /// </summary>
        /// <param name="objModel">相应的对象映射模型</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool Delete(GreatB2C.Model.Contacter objModel)
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Contacter]"
                            + " WHERE "
                            + "[ContacterID] = @ContacterID"
                            ;

            try
            {
                SqlParameter[] param = GetParameters(objModel);

                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据ID列表批量删除记录
        /// 数据格式：1,2,3,4,5
        /// </summary>
        /// <param name="strList">ID列表</param>
        /// <returns>0-操作成功 , errnum-操作失败，返回错误数</returns>
        public bool DeleteList(string strList)
        {
            string Sql = "DELETE FROM "
                            + "[dbo].[" + Prefix + "Contacter]"
                            + " WHERE "
                            + "[ContacterID] in ('" + strList + "')"
                            ;

            try
            {
                int affect = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);

                if (affect == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

        #endregion
    }
}
